This article was originally published by ArchSmarter.
Have you ever played the party game “telephone”? You know the one, where you tell something to the person next to you and they pass it on to the person next to them and so on down the line. Inevitably, your original message is badly mangled and misappropriated by the time it gets back to you. Everyone gets a good laugh at how far the end message is from your original one.
Now imagine that’s your project getting mangled and misappropriated. Not so funny now. But that’s often what happens when we have to translate models and files from one format to another.
All that translation means important information is likely to get lost. Fortunately, there’s a way to avoid this loss of data. And it comes from an unlikely source.
Excel.
Yep, you heard me right. That boring old spreadsheet software is really a superhero in disguise.
Excel to the Rescue
I recently worked on a project that relied heavily on Excel to translate geometrical data from one format to another. Excel was the perfect tool for the job.
I was consulting with a facade engineer. He was hired by a fabricator to engineer and develop fabrication drawings for a complex sheet metal facade for a parking garage.
The facade was designed in Rhino using Grasshopper. It consists of 900 folded sheet metal panels. The folding pattern varies across the facade as does the perforation pattern on the metal. There’s some repetition of panel types but not a lot.
The Rhino model was brought into Revit to develop construction documents. The Revit model, while sufficient for CDs, wasn’t precise enough for fabrication. So the facade engineer remodeled it in CATIA. This model definitely was precise.
The challenge was translating the data from the CATIA model into the tables and drawings the fabricators would use to actually construct the panels.
The geometrical data was easily extracted to Excel but it needed some serious reformatting. Also, a lot of the dimensions coming out of model were linear. This was good but the fabricator needed to know the angle between the panel segments. They’d plug this information into their bending machine to create the panels.
Using Excel, I wrote a series of macros that reformatted the CATIA data into a new table that listed the dimensions of each panel. This table included formulas that calculated the angles for each panel.
So far so good.
I then wrote another macro that used the dimensional data to create a summary table of panel types. We could now determine how many panel types there were and which types had the most panels.
This is when things got interesting.
Using this data, I hopped over to AutoCAD and wrote a macro that read the Excel summary table and generated drawings for each panel based on a template.
The macro inserted the dimensional data into a table in each drawing.
All told it took the macro less than 10 minutes to generate all 300 of the panel drawings.
Pretty cool!
Why did this work out so well? Here are 3 reasons why Excel is the ultimate BIM sidekick.
1. Excel is universal
Nearly all BIM and CAD applications can export data in text or CSV format. Many can even export directly to Excel. This universality means it’s easy to get some kind of data out of the software. It might not be in the format you need, as in the case with the CATIA data, but you can usually reformat it as needed.
Even if there’s no direct way to export to Excel, there may be a way to get the data out using macros or the software’s API. For example, Revit doesn’t link directly to Excel out-of-the-box but there are a number of other ways to export data, as I outlined in the post.
2. Excel loves data
One of the major reasons why Excel works so well with BIM is the reason many people think it’s so boring – all those rows and columns of numbers. Turns out, when you want to work with BIM data, that’s exactly what you need.
Most of the time, we need to organize categories of elements. Think structural columns or doors or rooms. This data is best represented in tabular format. Excel includes some advanced tools for summarizing this data, such as pivot tables.
Also, the ability to write complex formulas extends Excel’s usefulness considerably. Sure, you can create formulas in Revit but they’re nowhere near as powerful as those in Excel. If you need to do some heavy calculations, it’s much easier to export to Excel, do the calculations there, then bring the results back into Revit.
3. Excel is super easy to automate
One of the first programs I ever wrote was an Excel macro. They’re really easy to write. Excel macros are written using Visual Basic for Applications or VBA. Though it’s not as powerful as C# or VB.Net, VBA is user-friendly and easy to learn.
What’s better is that Excel has a macro recorder that records your actions on the screen and translates them to VBA code. If you’re not sure how to do something in a macro, simply record a macro while doing those actions. The code will get generated automatically.
The macros I wrote for the project started simple but got more complex over time. This turned out to be a good thing. I was able to easily modify the code to accommodate changes and recreating the tables was real easy.
The best part was generating the AutoCAD drawings off the Excel data. There’s something very satisfying seeing 300 CAD files created automatically with the press of a button. Also, AutoCAD uses VBA so there was good common ground between the two sets of macros.
Best Friends Forever
Given the data-intensive nature of BIM, it’s unlikely that Excel will be replaced as BIM’s ultimate sidekick. With that said, I do hope that software companies recognize the importance of working with data directly and create better two-way connections with Excel. Just like in Star Wars, Han puts up a better fight when he has Chewie at his side.
